package storage

import (
    "fmt"
    "spam3uk/internal/models"
    "time"
    "gorm.io/gorm/clause"
)



// UpsertTrimInfo 插入或更新trim信息
func (s *MySQLStorage) UpsertTrimInfo(trim *models.TrimInfo) error {
    now := time.Now()
    trim.ScrapedAt = now
    // 基于 (model_name, trim_name) 的幂等 Upsert
    return s.db.
        Where("model_name = ? AND trim_name = ?", trim.ModelName, trim.TrimName).
        Assign(map[string]interface{}{
            "trim_display_name":        trim.TrimDisplayName,
            "doors":                    trim.Doors,
            "price_from":               trim.PriceFrom,
            "currency":                 trim.Currency,
            "battery_capacity":         trim.BatteryCapacity,
            "energy_consumption":       trim.EnergyConsumption,
            "co2_emissions":            trim.CO2Emissions,
            "charging_time":            trim.ChargingTime,
            "range_miles":              trim.RangeMiles,
            "image_url":                trim.ImageURL,
            "configure_url":            trim.ConfigureURL,
            "see_full_specs_available": trim.SeeFullSpecsAvailable,
            "specifications":           trim.Specifications,
            "specs_metadata":           trim.SpecsMetadata,
            "scraped_at":               now,
            "updated_at":               now,
        }).
        FirstOrCreate(trim).Error
}

// 🔥 新增: UpsertTrimWithJSONSpecs 插入或更新包含JSON规格的trim信息
func (s *MySQLStorage) UpsertTrimWithJSONSpecs(trim *models.TrimInfo, specs []models.TrimSpecsInfo) error {
	if len(specs) == 0 {
		// 如果没有规格数据，只保存基本信息
		return s.UpsertTrimInfo(trim)
	}

	// 将关系型规格数据转换为JSON格式
	trimSpecs, metadata := s.convertSpecsToJSON(specs, trim.ModelName, trim.TrimName)

	// 设置JSON字段
	trim.Specifications = trimSpecs
	trim.SpecsMetadata = metadata

	// 保存到数据库
	return s.UpsertTrimInfo(trim)
}

// 🔥 新增: convertSpecsToJSON 将关系型规格数据转换为JSON格式
func (s *MySQLStorage) convertSpecsToJSON(specs []models.TrimSpecsInfo, modelName, trimName string) (models.TrimSpecifications, models.TrimSpecsMetadata) {
	// 按分类组织规格数据
	categoriesMap := make(map[string]models.SpecCategory)

	// 分类排序映射
    categoryOrder := map[string]int{
        "Performance": 1,
        "Battery":     2,
        "Charging":    3,
        "Powertrain":  4,
        "Dimensions":  5,
        "Safety":      6,
        "Technology":  7,
        "Comfort":     8,
        "Features":    9,
    }

	for _, spec := range specs {
		category := spec.SpecCategory

		// 如果分类不存在，创建新分类
		if _, exists := categoriesMap[category]; !exists {
			order := categoryOrder[category]
			if order == 0 {
				order = 99 // 未知分类放在最后
			}
			categoriesMap[category] = models.SpecCategory{
				CategoryOrder: order,
				Items:         []models.SpecItem{},
			}
		}

		// 添加规格项
		item := spec.ConvertToSpecItem()
		cat := categoriesMap[category]
		cat.Items = append(cat.Items, item)
		categoriesMap[category] = cat
	}

	// 构建TrimSpecifications
	trimSpecs := models.TrimSpecifications{
		Categories: categoriesMap,
	}

	// 构建TrimSpecsMetadata
	metadata := models.TrimSpecsMetadata{
		TotalCategories:  len(categoriesMap),
		TotalSpecs:       len(specs),
		ScrapedAt:        time.Now(),
		SourceURL:        "",
		ScrapeMethod:     "converted_from_relational",
		DataCompleteness: "100%",
	}

	return trimSpecs, metadata
}

// 🔥 保留旧方法以兼容现有代码
// UpsertTrimSpecs 插入或更新trim规格信息 (兼容性方法)
func (s *MySQLStorage) UpsertTrimSpecs(spec *models.TrimSpecsInfo) error {
	// 设置当前时间
	now := time.Now()
	spec.ScrapedAt = now

	// 使用GORM的Save方法
	return s.db.Save(spec).Error
}

// GetTrimsByModel 根据model名称获取所有trim信息
func (s *MySQLStorage) GetTrimsByModel(modelName string) ([]models.TrimInfo, error) {
	var trims []models.TrimInfo
	err := s.db.Where("model_name = ?", modelName).
		Order("price_from ASC, trim_name ASC").
		Find(&trims).Error
	if err != nil {
		return nil, fmt.Errorf("failed to get trims for model '%s': %w", modelName, err)
	}
	return trims, nil
}

// 🔥 保留旧方法以兼容现有代码
// GetTrimSpecsByTrim 根据trim名称和model名称获取详细规格 (兼容性方法)
func (s *MySQLStorage) GetTrimSpecsByTrim(trimName, modelName string) (map[string][]models.TrimSpecsInfo, error) {
	var specs []models.TrimSpecsInfo
	err := s.db.Where("trim_name = ? AND model_name = ?", trimName, modelName).
		Order("spec_category ASC, display_order ASC, spec_name ASC").
		Find(&specs).Error
	if err != nil {
		return nil, fmt.Errorf("failed to get specs for trim '%s' model '%s': %w", trimName, modelName, err)
	}

	// 按照spec_category分组
	grouped := make(map[string][]models.TrimSpecsInfo)
	for _, spec := range specs {
		grouped[spec.SpecCategory] = append(grouped[spec.SpecCategory], spec)
	}

	return grouped, nil
}

// 🔥 新增: GetTrimJSONSpecs 获取trim的JSON格式规格数据
func (s *MySQLStorage) GetTrimJSONSpecs(trimName, modelName string) (*models.TrimSpecifications, *models.TrimSpecsMetadata, error) {
	var trim models.TrimInfo
	err := s.db.Where("trim_name = ? AND model_name = ?", trimName, modelName).First(&trim).Error
	if err != nil {
		return nil, nil, fmt.Errorf("failed to get trim '%s' model '%s': %w", trimName, modelName, err)
	}

	return &trim.Specifications, &trim.SpecsMetadata, nil
}

// 🔥 升级: GetTrimsWithSpecs 获取完整的trim信息，优先使用JSON规格
func (s *MySQLStorage) GetTrimsWithSpecs(modelName string) ([]models.TrimWithSpecs, error) {
	// 获取trim基本信息
	trims, err := s.GetTrimsByModel(modelName)
	if err != nil {
		return nil, err
	}

	var result []models.TrimWithSpecs
	for _, trim := range trims {
		// 🔥 优先使用JSON格式的规格数据
		var specs map[string][]models.TrimSpecsInfo
		var specsCount int

		if len(trim.Specifications.Categories) > 0 {
			// 使用JSON格式的规格数据，转换为兼容格式
			specs = s.convertJSONToLegacySpecs(trim.Specifications)
			specsCount = trim.SpecsMetadata.TotalSpecs
		} else {
			// 回退到旧的关系型数据
			legacySpecs, err := s.GetTrimSpecsByTrim(trim.TrimName, trim.ModelName)
			if err != nil {
				// 如果获取规格失败，仍然返回trim基本信息，但不包含规格
				legacySpecs = make(map[string][]models.TrimSpecsInfo)
			}
			specs = legacySpecs

			// 计算规格总数
			for _, categorySpecs := range specs {
				specsCount += len(categorySpecs)
			}
		}

		trimWithSpecs := models.TrimWithSpecs{
			TrimInfo:       trim,
			Specifications: specs,
			SpecsCount:     specsCount,
		}
		result = append(result, trimWithSpecs)
	}

	return result, nil
}

// 🔥 新增: convertJSONToLegacySpecs 将JSON格式转换为旧的关系型格式（兼容性方法）
func (s *MySQLStorage) convertJSONToLegacySpecs(jsonSpecs models.TrimSpecifications) map[string][]models.TrimSpecsInfo {
	legacySpecs := make(map[string][]models.TrimSpecsInfo)

	for categoryName, category := range jsonSpecs.Categories {
		var categorySpecs []models.TrimSpecsInfo

		for _, item := range category.Items {
			spec := models.TrimSpecsInfo{
				SpecCategory: categoryName,
				SpecName:     item.Name,
				SpecValue:    item.Value,
				SpecUnit:     item.Unit,
				DisplayOrder: item.DisplayOrder,
			}
			categorySpecs = append(categorySpecs, spec)
		}

		legacySpecs[categoryName] = categorySpecs
	}

	return legacySpecs
}

// DeleteTrimByModel 删除指定model的所有trim信息（用于重新抓取）
func (s *MySQLStorage) DeleteTrimByModel(modelName string) error {
	// 开启事务
	tx := s.db.Begin()
	if tx.Error != nil {
		return tx.Error
	}

	// 删除规格信息（旧表，保留兼容性）
	if err := tx.Where("model_name = ?", modelName).Delete(&models.TrimSpecsInfo{}).Error; err != nil {
		tx.Rollback()
		return fmt.Errorf("failed to delete trim specs: %w", err)
	}

	// 删除trim信息（JSON字段会自动清空）
	if err := tx.Where("model_name = ?", modelName).Delete(&models.TrimInfo{}).Error; err != nil {
		tx.Rollback()
		return fmt.Errorf("failed to delete trim info: %w", err)
	}

	return tx.Commit().Error
}

// 🔥 保留旧方法以兼容现有代码
// BatchUpsertTrimSpecs 批量插入或更新trim规格信息 (兼容性方法)
func (s *MySQLStorage) BatchUpsertTrimSpecs(specs []models.TrimSpecsInfo) error {
	if len(specs) == 0 {
		return nil
	}

	// 设置当前时间
	now := time.Now()
	for i := range specs {
		specs[i].ScrapedAt = now
	}

	// 🔥 同时保存到关系型表和JSON字段
    // 1. 保存到关系型表（兼容性，带去重 Upsert）
    if err := s.db.Clauses(
        clause.OnConflict{
            Columns:   []clause.Column{{Name: "model_name"}, {Name: "trim_name"}, {Name: "spec_category"}, {Name: "spec_name"}},
            DoUpdates: clause.AssignmentColumns([]string{"spec_value", "spec_unit", "display_order", "updated_at", "scraped_at"}),
        },
    ).Create(&specs).Error; err != nil {
		return fmt.Errorf("failed to save specs to relational table: %w", err)
	}

	// 2. 按trim分组并更新JSON字段
	trimSpecs := make(map[string][]models.TrimSpecsInfo)
	for _, spec := range specs {
		key := fmt.Sprintf("%s:%s", spec.ModelName, spec.TrimName)
		trimSpecs[key] = append(trimSpecs[key], spec)
	}

	// 3. 更新每个trim的JSON字段
	for _, specsGroup := range trimSpecs {
		// 解析model和trim名称
		var modelName, trimName string
		if len(specsGroup) > 0 {
			modelName = specsGroup[0].ModelName
			trimName = specsGroup[0].TrimName
		}

		// 获取现有trim信息
		var trim models.TrimInfo
		err := s.db.Where("model_name = ? AND trim_name = ?", modelName, trimName).First(&trim).Error
		if err != nil {
			continue // 如果trim不存在，跳过JSON更新
		}

		// 转换为JSON格式并更新
		jsonSpecs, metadata := s.convertSpecsToJSON(specsGroup, modelName, trimName)
		trim.Specifications = jsonSpecs
		trim.SpecsMetadata = metadata

		s.db.Save(&trim)
	}

	return nil
}

// GetTrimCount 获取指定model的trim数量
func (s *MySQLStorage) GetTrimCount(modelName string) (int64, error) {
	var count int64
	err := s.db.Model(&models.TrimInfo{}).Where("model_name = ?", modelName).Count(&count).Error
	return count, err
}

// 🔥 保留旧方法以兼容现有代码
// GetTrimSpecsCount 获取指定trim的规格数量 (兼容性方法)
func (s *MySQLStorage) GetTrimSpecsCount(trimName, modelName string) (int64, error) {
	// 优先从JSON字段获取
	trim := models.TrimInfo{}
	err := s.db.Where("trim_name = ? AND model_name = ?", trimName, modelName).First(&trim).Error
	if err == nil && trim.SpecsMetadata.TotalSpecs > 0 {
		return int64(trim.SpecsMetadata.TotalSpecs), nil
	}

	// 回退到关系型表计数
	var count int64
	err = s.db.Model(&models.TrimSpecsInfo{}).
		Where("trim_name = ? AND model_name = ?", trimName, modelName).
		Count(&count).Error
	return count, err
}
